package com.ignou.mca.datasource;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.ignou.mca.beans.CandidatesFormBean;
import com.ignou.mca.beans.Message;
import com.ignou.mca.beans.PartiesFormBean;
import com.ignou.mca.beans.PositionsFormBean;
import com.ignou.mca.beans.UserVO;
import com.ignou.mca.beans.UsersFormBean;
import com.ignou.mca.beans.VotersFormBean;
import com.ignou.mca.util.RandamNumberGeneration;
import com.ignou.mca.util.SessionUtil;

public class DataBaseUtilities {

	Connection conn = null;
	
	public void DataBaseUtilities()
	{
		conn = getConnection();
	}
	
	public Connection getConnection()
	{
		String userName = "root";
        String password = "root";
        String url = "jdbc:mysql://localhost:3306/onlinevoting";
        try {
			Class.forName ("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url, userName, password);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        
        System.out.println ("");
        return conn;
	}
	
	public UserVO doLogin(String userName,String Password,Message message)
	{
		UserVO uVo = null;
		
			if(conn==null) conn = getConnection();
			String activeStatus = "";
			
			try {
				Statement st = conn.createStatement();
				ResultSet rs = st.executeQuery("select userName,Password,role,activestatus from users where UserName='"+userName+"'");
				if(rs.next()){
						uVo = new UserVO();
						uVo.setUserName(rs.getString(1));
						uVo.setPassword(rs.getString(2));
						uVo.setRole(rs.getString(3));
						uVo.setActiveStatus(rs.getString(4));
						return uVo;
					}
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		message.setMessage("Invalid Login");
		return uVo;
	}
	
	public boolean checkUsername(String userName,String email) {
		try {
			if(conn==null) conn = getConnection();
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("select * from users where Username like '"
							+ userName + "'");
			boolean exists = false;
			while (rs.next()) {
				exists = true;
			}
			return exists;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public String checkSecurityAnswer(String userName,String securityAnswer,String email) {
		try {
			if(conn==null) conn = getConnection();
			Statement st = conn.createStatement();
			ResultSet rs = st
					.executeQuery("select securityAnswer,password,email from users where uesrName like '"
							+ userName + "'");
			String password = "";
			String mailId = "";
			
			while (rs.next()) {
				securityAnswer = rs.getString(1);
				password = rs.getString(2);
				mailId = rs.getString(3);
			}
			
			if(securityAnswer.equalsIgnoreCase(securityAnswer) && mailId.equals(email))
			{
				SessionUtil.sendMail(email, userName, password, "forgotPwd","");
				return password;
			}else
			{
				return "";
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}
	
	public ResultSet viewAllParties()
	{
		if(conn==null) conn = getConnection();
		Statement st;
		try {
			st = conn.createStatement();
			ResultSet rs = st.executeQuery("select partyID,PartyName,Description,Phone from parties");
			return rs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public ResultSet viewAllPositions()
	{
		if(conn==null) conn = getConnection();
		Statement st;
		try {
			st = conn.createStatement();
			ResultSet rs = st.executeQuery("select * from positions");
			return rs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public ResultSet showVoting()
	{
		if(conn!=null) conn = getConnection();
		Statement st;
		try {
			st = conn.createStatement();
			//ResultSet rs = st.executeQuery("select p.PartyID,p.PartyName,c.candidateName,c.positionId from parties p, candidates c where p.partyId=c.partyId");
			ResultSet rs = st.executeQuery("select * from parties");
			return rs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}

	public ResultSet getUserDetails()
	{
		if(conn==null) conn = getConnection();
		Statement st;
		try {
			st = conn.createStatement();
			//ResultSet rs = st.executeQuery("select p.PartyID,p.PartyName,c.candidateName,c.positionId from parties p, candidates c where p.partyId=c.partyId");
			ResultSet rs = st.executeQuery("select * from users");
			return rs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}

	
}